/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.core.service.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import javax.sql.DataSource;

import org.apache.commons.logging.*;

public class DBUtil {
	private static Log log = LogFactory.getLog(DBUtil.class);

	DataSource dataSource = null;
	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	String url;

	public DBUtil(String url) {
		this.url = url;
	}

	public Connection getConnection() {
		return DBConnection.getInstance(url).getConnection();
	}

	public DBSet query(String query) {
		DBSet DBSet = new DBSet();
		Connection connection = getConnection();
		Statement statement = null;
		log.info("[" + this.df.format(new Date()) + "]:" + query);

		try {
			statement = connection.createStatement();

			DBSet = wrapper(statement.executeQuery(query));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException ex) {
				}
				connection = null;
			}
		}
		return DBSet;
	}

	public DBSet wrapper(ResultSet resultSet) {
		DBSet objDBSet = new DBSet();
		try {
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

			int intLineNum = resultSetMetaData.getColumnCount();

			while (resultSet.next()) {
				DBRow DBRow = new DBRow();
				for (int i = 1; i <= intLineNum; ++i) {
					DBRow.addCell(resultSetMetaData.getColumnName(i)
							.toUpperCase(), resultSet.getObject(i));
				}
				objDBSet.addDBRow(DBRow);
			}
		} catch (SQLException ex) {
		}
		return objDBSet;
	}

	public int executeSQL(List<String> sqls) {
		int ret = 2;
		Connection connection = null;
		PreparedStatement statement = null;
		if ((sqls == null) || (sqls.size() == 0)) {
			return ret;
		}
		try {
			connection = getConnection();
			connection.setAutoCommit(false);
			int slength = sqls.size();
			for (int i = 0; i < slength; ++i) {
				statement = connection.prepareStatement(sqls.get(i).toString());

				System.out.println(sqls.get(i).toString());

				statement.execute();
			}
			connection.commit();
		} catch (Exception ex) {

			ex.printStackTrace();
		} finally {
			try {
				ret = 0;
				statement.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				connection.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return ret;
	}

}
